

create database OADatabase
go

use OADatabase 


if exists(select 1 from sys.sysforeignkey where role='FK_APPLY_REFERENCE_PLAN') then
    alter table Apply
       delete foreign key FK_APPLY_REFERENCE_PLAN
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_DEPARTME_REFERENCE_PLAN') then
    alter table Department
       delete foreign key FK_DEPARTME_REFERENCE_PLAN
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_EMPLOYEE_REFERENCE_DEPARTME') then
    alter table EmployeesInfo
       delete foreign key FK_EMPLOYEE_REFERENCE_DEPARTME
end if;

drop table if exists Apply;

drop table if exists Department;

drop table if exists EmployeesInfo;

drop table if exists Plan;

drop table if exists UserInfo;

/*==============================================================*/
/* Table: Apply                                                 */
/*==============================================================*/
create table Apply 
(
   "No"                 int                            not null,
   Uid                  nvarcahr(80)                   null,
   "date"               datetime                       null,
   day                  nvarcahr(80)                   null,
   people               nvarcahr(80)                   null,
   address              nvarcahr(80)                   null,
   money                money                          null,
   mark                 nvarcahr(80)                   null,
   constraint PK_APPLY primary key clustered ("No")
);

/*==============================================================*/
/* Table: Department                                            */
/*==============================================================*/
create table Department 
(
   num                  int                            not null,
   "No"                 int                            null,
   name                 nvarchar(80)                   null,
   leader               nvarchar(80)                   null,
   phone                nvarchar(80)                   null,
   mail                 nvarchar(80)                   null,
   constraint PK_DEPARTMENT primary key clustered (num)
);

/*==============================================================*/
/* Table: EmployeesInfo                                         */
/*==============================================================*/
create table EmployeesInfo 
(
   "No"                 int                            not null,
   num                  int                            null,
   EName                nvarchar(80)                   null,
   Post                 nvarchar(80)                   null,
   contact              nvarchar(80)                   null,
   Pay                  nvarchar(80)                   null,
   constraint PK_EMPLOYEESINFO primary key clustered ("No")
);

/*==============================================================*/
/* Table: Plan                                                  */
/*==============================================================*/
create table Plan 
(
   "No"                 int                            not null,
   task                 nvarchar(80)                   null,
   mark                 nvarchar(80)                   null,
   constraint PK_PLAN primary key clustered ("No")
);

/*==============================================================*/
/* Table: UserInfo                                              */
/*==============================================================*/
create table UserInfo 
(
   Uid                  int                            not null,
   Uname                nvarchar(80)                   null,
   Account              nvarchar(80)                   null,
   Phone                nvarchar(80)                   null,
   Address              nvarchar(80)                   null,
   constraint PK_USERINFO primary key clustered (Uid)
);

alter table Apply
   add constraint FK_APPLY_REFERENCE_PLAN foreign key ("No")
      references Plan ("No")
      on update restrict
      on delete restrict;

alter table Department
   add constraint FK_DEPARTME_REFERENCE_PLAN foreign key ("No")
      references Plan ("No")
      on update restrict
      on delete restrict;

alter table EmployeesInfo
   add constraint FK_EMPLOYEE_REFERENCE_DEPARTME foreign key (num)
      references Department (num)
      on update restrict
      on delete restrict;
